/**
 * 
 */
package cs342.project1.mathNotebook.db;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

/**
 * Maintains a connection to a database.  Database connection parameters can be specified in a properties file
 * or properties object.  The PROP_KEY_* constants are the properties keys that are used to extract
 * connection information from the properties file or object.
 * @author Aaron Heise <heisea26@uwosh.edu>
 */
public class DatabaseContext {
	/**
	 * Properties key for JDBC URL to connect to
	 */
	public static final String PROP_KEY_JDBC_URL     = "JDBC_URL";
	/**
	 * Properties key for database username
	 */
	public static final String PROP_KEY_DB_USERNAME  = "DB_USERNAME";
	/**
	 * Properties key for database password
	 */
	public static final String PROP_KEY_DB_PASSWORD  = "DB_PASSWORD";
	/**
	 * Properties key for a table prefix to use
	 */
	public static final String PROP_KEY_TABLE_PREFIX = "TABLE_PREFIX";
	
	/**
	 * SQL to drop table. Uses a String.format() argument for table name
	 */
	protected static final String DROP_TABLE_SQL = "DROP TABLE %s"; // arg0 := table name to drop
	
	/**
	 * object database properties
	 */
	protected Properties properties  = null;
	/**
	 * 
	 */
	protected Connection connection  = null;
	/**
	 * prepended to table names as requested
	 */
	protected String     tablePrefix = "";
	
	/** loads a properties file into a new Properties object
	 * @param filename filename to load properties from
	 * @return properties object containing properties from file
	 * @throws FileNotFoundException if file does not exist
	 * @throws IOException if file cannot be read
	 */
	protected static Properties loadPropertiesFromFile(String filename) throws FileNotFoundException, IOException {
		Properties p = new Properties();
		p.load(new FileInputStream(filename));
		return p;
	}
	
	/** connects database if not connected
	 * @return the connection to execute queries against
	 * @throws DatabaseUnconnectableException if database cannot be connected
	 */
	protected Connection checkConnectDatabase() throws DatabaseUnconnectableException {
		if(connection == null || !this.isConnected() ) {
			try {
				//register driver
				Class.forName("com.mysql.jdbc.Driver").newInstance();
			} catch (Exception e) {
				throw new DatabaseUnconnectableException("Could not register driver", e);
			}
			
			Connection c = null;
			try {
				c = DriverManager.getConnection(this.properties.getProperty(PROP_KEY_JDBC_URL), 
												this.properties.getProperty(PROP_KEY_DB_USERNAME),
												this.properties.getProperty(PROP_KEY_DB_PASSWORD));
			} catch (Exception e) {
				throw new DatabaseUnconnectableException("Could not connect to database.", e);
			}
			
			connection = c;
		}
		return connection;
	}
	
	/** Creates a context from the properties contained in propertiesFileName.
	 * Checks that database can be connected.
	 * @param propertiesFileName path to properties file
	 * @throws DatabaseUnconnectableException cannot connect to database
	 * @throws FileNotFoundException properties file does not exist
	 * @throws IOException cannot read properties file
	 */
	public DatabaseContext(String propertiesFileName) throws DatabaseUnconnectableException, FileNotFoundException, IOException {
		this(loadPropertiesFromFile(propertiesFileName));
	}
	
	/** Creates a context from the properties contained in properties.
	 * Checks that database can be connected.
	 * @param properties
	 * @throws DatabaseUnconnectableException cannot connect to database
	 */
	public DatabaseContext(Properties properties) throws DatabaseUnconnectableException {
		this.properties = properties;
		String s = this.properties.getProperty(PROP_KEY_TABLE_PREFIX);
		if(s != null) {
			this.tablePrefix = s;
		}
		this.checkConnectDatabase();
	}
	
	/** Get a valid java.sql.Connection for this context.  If current connection is dead, create a new one.
	 * @return connection to database behind this context
	 * @throws DatabaseUnconnectableException cannot connect to database
	 */
	public Connection getConnection() throws DatabaseUnconnectableException {
		return this.checkConnectDatabase();
	}
	
	/** Check if database is currently connected
	 * @deprecated This method will become protected, is not needed in the public API
	 * @return true if there is a connection and it is not closed.
	 */
	public boolean isConnected() {
		boolean isConnected = connection != null;
		if(isConnected) {
			try {
				isConnected = isConnected && !connection.isClosed();
			} catch (Exception e) {
				isConnected = false;
			}
		}
		return (isConnected);
	}
	
	/** Clean up connection. */
	public void close() {
		if(this.isConnected()) {
			try {
				connection.close();
			} catch (Exception e) {
			}
			
			connection = null;
		}
	}
	
	/** Add table prefix to tableName for use in SQL queries
	 * @param tableName table name to prefix
	 * @return tableName with prefix
	 */
	public String getPrefixedTableName(String tableName) {
		return (this.tablePrefix != null && this.tablePrefix.length() > 0 ? this.tablePrefix + "_" + tableName : tableName);
	}
	
	/** Execute an SQL statement and ignore the results.
	 * @param sql SQL to execute
	 * @throws DatabaseException if SQL will not execute
	 */
	public void executeSQLNoResults(String sql) throws DatabaseException {
		
		try {
			PreparedStatement stmt = this.checkConnectDatabase().prepareStatement(sql);
			stmt.execute();
		} catch (SQLException e) {
			throw new DatabaseExecutionException("Error executing SQL: " + sql, e);
		}
	}
	
	/** Drops tableName from database after adding prefix
	 * @param tableName tableName to drop
	 * @throws DatabaseException if table cannot be dropped
	 */
	public void dropTable(String tableName) throws DatabaseException {
		String actualTableName = this.getPrefixedTableName(tableName);
		System.out.println("Dropping table: " + actualTableName);
		this.executeSQLNoResults(String.format(DROP_TABLE_SQL, actualTableName));
	}
}
